| MaxCompute窗口函数介绍 | 您所在的位置：网站首页 › hive 标准差 › MaxCompute窗口函数介绍 |
|
MaxCompute SQL支持的窗口函数如下。 函数 功能 AVG 对窗口中的数据求平均值。 CLUSTER_SAMPLE 用户随机抽样。返回True表示该行数据被抽中。 COUNT 计算窗口中的记录数。 CUME_DIST 计算累计分布。 DENSE_RANK 计算排名。排名是连续的。 FIRST_VALUE 取当前行所对应窗口的第一条数据的值。 LAG 取当前行往前（朝分区头部方向）第N行数据的值。 LAST_VALUE 取当前行所对应窗口的最后一条数据的值。 LEAD 取当前行往后（朝分区尾部方向）第N行数据的值。 MAX 计算窗口中的最大值。 MEDIAN 计算窗口中的中位数。 MIN 计算窗口中的最小值。 NTILE 将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。 NTH_VALUE 取当前行所对应窗口的第N条数据的值。 PERCENT_RANK 计算排名。输出百分比格式。 RANK 计算排名。排名可能不连续。 ROW_NUMBER 计算行号。从1开始递增。 STDDEV 计算总体标准差。是STDDEV_POP的别名。 STDDEV_SAMP 计算样本标准差。 SUM 对窗口中的数据求和。 使用限制窗口函数的使用限制如下： 窗口函数只能出现在select语句中。 窗口函数中不能嵌套使用窗口函数和聚合函数。 窗口函数不能和同级别的聚合函数一起使用。 窗口函数语法窗口函数的语法声明如下。 ([distinct][ [, ...]]) over () ([distinct][ [, ...]]) overfunction_name：内建窗口函数、聚合函数或用户自定义聚合函数UDAF。 expression：函数格式，具体格式以实际函数语法为准。 windowing_definition：窗口定义。详细语法格式请参见windowing_definition。 window_name：窗口名称。您可以使用window关键字自定义窗口，为windowing_definition定义名称。自定义语句（named_window_def）如下： window as ()自定义语句在SQL中的位置如下： select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]windowing_definitionwindowing_definition的语法声明如下。 --partition_clause： [partition by [, ...]] --orderby_clause： [order by [asc|desc][nulls {first|last}] [, ...]] []在SELECT语句中加入窗口函数，计算窗口函数的结果时，数据会按照窗口定义中的partition by和order by语句进行分区和排序。如果没有partition by语句，则仅有一个分区，包含全部数据。如果没有order by语句，则分区内的数据会按照任意顺序排布，最终生成一个数据流。之后对于每一行数据（当前行），会按照窗口定义中的frame_clause从数据流中截取一段数据，构成当前行的窗口。窗口函数会根据窗口中包含的数据，计算得到窗口函数针对当前行对应的输出结果。 partition by [, ...]：可选。指定分区。分区列的值相同的行被视为在同一个窗口内。详细格式请参见表操作。 order by [asc|desc][nulls {first|last}] [, ...]：可选。指定数据在一个窗口内如何排序。 说明当遇到相同的order by值时，排序结果不稳定。为减少随机性，应当尽可能保持order by值的唯一性。 frame_clause：可选。用于确定数据边界，更多frame_clause信息，请参见frame_clause。 filter_clausefilter_clause的语法声明如下。 FILTER (WHERE filter_condition)其中filter_condition为布尔表达式，和select ... from ... where语句中的where用法完全相同。 如果提供了FILTER子句，则只有filter_condition值为true的行才会包含在窗口frame中。对于聚合窗口函数（包括：COUNT、SUM、AVG、MAX、MIN、WM_CONCAT等）仍为每行返回一个值，但FILTER表达式计算结果为true以外的值（即NULL或false，NULL按false处理）不会包含在任何行的窗口frame中。 示例 数据准备 --创建表 CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC; --插入数据 insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700); --查询mf_window_fun表数据 select * from mf_window_fun; --返回结果 +------------+------------+ | key | value | +------------+------------+ | 1 | 100 | | 2 | 200 | | 1 | 150 | | 2 | 250 | | 3 | 300 | | 4 | 400 | | 5 | 500 | | 6 | 600 | | 7 | 700 | +------------+------------+查询窗口内value值大于100的行的累积和 select key,sum(value) filter(where value > 100) over (partition by key order by key) from mf_window_fun;返回结果： +------------+------------+ | key | _c1 | +------------+------------+ | 1 | NULL | --跳过计算 | 1 | 150 | | 2 | 200 | | 2 | 450 | | 3 | 300 | | 4 | 400 | | 5 | 500 | | 6 | 600 | | 7 | 700 | +------------+------------+说明FILTER子句并不会在查询结果中去掉不满足filter_condition的行，只是在窗口函数计算时认为这一行不存在。如果想要去掉相应的行，仍然需要在select ... from ... where的条件中指定。而这一行的窗口函数值也并不是0或者NULL，而是沿用其前一行的窗口函数值。 只有当窗口函数为聚合类函数（包括：COUNT、SUM、AVG、MAX、MIN、WM_CONCAT等）时，才能使用FILTER子句，非聚合类函数（例如：RANK、ROW_NUMBER、NTILE）不能使用FILTER子句，否则会出现语法错误。 在窗口函数中使用FILTER语法时需要开启如下开关：set odps.sql.window.function.newimpl=true;。 frame_clauseframe_clause的语法声明如下。 --格式一。 {ROWS|RANGE|GROUPS} [] --格式二。 {ROWS|RANGE|GROUPS} between and []frame_clause是一个闭区间，用于确定数据边界，包含frame_start和frame_end位置的数据行。 ROWS|RANGE|GROUPS：必填。frame_clause的类型，各类型的frame_start和frame_end实现规则不相同。其中： ROWS类型：通过数据行数确定数据边界。 RANGE类型：通过比较order by列值的大小关系来确定数据边界。一般在窗口定义中会指定order by，未指定order by时，一个分区中的所有数据行具有相同的order by列值。NULL与NULL被认为是相等的。 GROUPS：一个分区中所有具有相同order by列值的数据组成一个GROUP。未指定order by时，分区中的所有数据组成一个GROUP。NULL与NULL被认为是相等的。 frame_start、frame_end：表示窗口的起始和终止边界。frame_start必填。frame_end可选，省略时默认值为CURRENT ROW。 frame_start确定的位置必须在frame_end确定的位置的前面，或者等于frame_end的位置，即frame_start相比frame_end更靠近分区头部。分区头部是指数据按窗口定义中的order by语句排序之后第1行数据的位置。ROWS、RANGE、GROUPS类型对应的取值范围及实现逻辑如下。 frame_clause类型 frame_start/frame_end取值 说明 ROWS、RANGE、GROUPS UNBOUNDED PRECEDING 表示分区的第一行，从1开始计数。 UNBOUNDED FOLLOWING 表示分区的最后一行。 ROWS CURRENT ROW 指当前行的位置。每一行数据都会对应一个窗口函数的结果值，当前行是指在给哪一行数据计算窗口函数的结果。 offset PRECEDING 指从当前行位置，向分区头部位置移动offset行的位置。例如0 PRECEDING指当前行，1 PRECEDING指前一行。offset必须为非负整数。 offset FOLLOWING 指从当前行位置，向分区尾部移动offset行的位置。例如0 FOLLOWING指当前行，1 FOLLOWING指下一行。offset必须为非负整数。 RANGE CURRENT ROW 作为frame_start时，指第一条与当前行具有相同order by列值的数据的位置。 作为frame_end时，指最后一条与当前行具有相同order by列值的数据的位置。 offset PRECEDING frame_start和frame_end的位置与order by的顺序相关。假设窗口按照X进行排序，Xi表示第i行数据对应的X值，Xc表示当前行数据对应X值。位置说明如下： 当order by为升序时： frame_start：指第一条满足Xc - Xi = offset数据的位置。 当order by为降序时： frame_start：指第一条满足Xi - Xc = offset数据的位置。 order by的列支持的数据类型为：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、DATETIME、DATE、TIMESTAMP。 日期类型数据的offset语法如下： N：表示N天或N秒。非负整数。对于DATETIME和TIMESTAMP，表示N秒；对于DATE，表示N天。 interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}：表示N年/月/日/小时/分钟/秒。例如INTERVAL '3' YEAR表示3年。 INTERVAL 'N-M' YEAR TO MONTH：表示N年M月。例如INTERVAL '1-3' YEAR TO MONTH表示1年3个月。 INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND：表示D天H小时M分钟S秒N纳秒。例如INTERVAL '1 2:3:4:5' DAY TO SECOND表示1天2小时3分钟4秒5纳秒。 offset FOLLOWING frame_start和frame_end的位置与order by的顺序相关。假设窗口按照X进行排序，Xi表示第i行数据对应的X值，Xc表示当前行数据对应X值。位置说明如下： 当order by为升序时： frame_start：指第一条满足Xi - Xc >= offset数据的位置。 frame_end：指最后一条满足Xi - Xc = offset数据的位置。 frame_end：指最后一条满足Xc - Xi |
| CopyRight 2018-2019 实验室设备网 版权所有 |